package com.atech.ebayadmin.invoice.dao.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import com.atech.ebayadmin.common.util.BaseDAOHibernate;
import com.atech.ebayadmin.common.pager.Pager;
import com.atech.ebayadmin.invoice.dao.IInvoiceDAO;
import com.atech.ebayadmin.invoice.model.Invoice;
import com.atech.ebayadmin.invoice.vo.InvoiceVO;

public class InvoiceDAO extends BaseDAOHibernate implements IInvoiceDAO{
	private static org.apache.commons.logging.Log log = org.apache.commons.logging.LogFactory.getLog(InvoiceDAO.class);
	@Override
	protected Class getObjClass() {
		return Invoice.class;
	}
	public List<Invoice> listInvoice(InvoiceVO vo, Pager pager) throws Exception{
		StringBuilder queryCountHql = new StringBuilder(this.queryCountHql(vo));	
		StringBuilder queryListHql = new StringBuilder(this.queryListHql(vo));
		Query queryCount = super.getSession().createQuery(queryCountHql.toString());
		Query queryList = super.getSession().createQuery(queryListHql.toString());
		int i =0;
		if(StringUtils.isNotBlank(vo.getTitle())){
			queryCount.setString(i, "%"+vo.getTitle().trim()+"%");
			queryList.setString(i, "%"+vo.getTitle().trim()+"%");
			i++;
		}
		if(StringUtils.isNotBlank(vo.getEBayAct())){
			queryCount.setString(i, vo.getEBayAct().trim());
			queryList.setString(i,  vo.getEBayAct().trim());
			i++;
		}
		if(StringUtils.isNotBlank(vo.getItemID())){
			queryCount.setString(i, "%"+vo.getItemID().trim()+"%");
			queryList.setString(i, "%"+vo.getItemID().trim()+"%");
			i++;
		}
		if(vo.getBegin_time() != null ){
			queryCount.setCalendar(i, vo.getBegin_time());
			queryList.setCalendar(i, vo.getBegin_time());
			i++;
		}
		if(vo.getEnd_time() != null ){
			queryCount.setCalendar(i, vo.getEnd_time());
			queryList.setCalendar(i, vo.getEnd_time());
			i++;
		}
		Number total = (Number)queryCount.uniqueResult();
		pager.setTotalRows(total.intValue());
		queryList.setFirstResult(pager.getStartRow());
		queryList.setMaxResults(pager.getPageSize());
		return queryList.list();
	}
	public List<Invoice> listInvoice(InvoiceVO vo) throws Exception{	
		StringBuilder queryListHql = new StringBuilder(this.queryListHql(vo));
		Query queryList = super.getSession().createQuery(queryListHql.toString());
		int i =0;
		if(StringUtils.isNotBlank(vo.getTitle())){
			queryList.setString(i, "%"+vo.getTitle().trim()+"%");
			i++;
		}
		if(StringUtils.isNotBlank(vo.getEBayAct())){
			queryList.setString(i,  vo.getEBayAct().trim());
			i++;
		}
		if(StringUtils.isNotBlank(vo.getItemID())){
			queryList.setString(i, "%"+vo.getItemID().trim()+"%");
			i++;
		}
		if(vo.getBegin_time() != null ){
			queryList.setCalendar(i, vo.getBegin_time());
			i++;
		}
		if(vo.getEnd_time() != null ){
			queryList.setCalendar(i, vo.getEnd_time());
			i++;
		}
		return queryList.list();
	}
	private String queryCountHql(InvoiceVO vo){
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ");
		sb.append(getObjClass().getName().toString());
		sb.append(this.createHqlString(vo));
		return sb.toString();
	}
	private String queryListHql(InvoiceVO vo){
		StringBuilder sb = new StringBuilder();
		sb.append("from ");
		sb.append(getObjClass().getName().toString());
		sb.append(this.createHqlString(vo));
		return sb.toString();
	}
	private String createHqlString(InvoiceVO vo){		
		StringBuilder sb = new StringBuilder();
		sb.append(" t where 1=1 ");
		if(StringUtils.isNotBlank(vo.getTitle())){
			sb.append(" and t.Title like ?").append("");
		}
		if(StringUtils.isNotBlank(vo.getEBayAct())){
			sb.append(" and t.ebayAct = ?").append("");
		}
		if(StringUtils.isNotBlank(vo.getItemID())){
			sb.append(" and t.ItemID like ?").append("");
		}
		if(vo.getBegin_time() != null ){
        	sb.append(" and t.Date>= ? ");
		}
		if(vo.getEnd_time() != null ){
    		sb.append(" and t.Date< ? ");
		}
		sb.append(" order by t.Date desc");
		return sb.toString();
	}

	public Invoice getInvoice(String id) throws Exception {
		Invoice po = (Invoice)super.findObjectById(id);
		return po;
	}

	public void updateInvoice(Invoice po) throws Exception {
		super.updateObject(po);
	}
	public void saveInvoice(Invoice po) throws Exception{
		super.getHibernateTemplate().save(po);
	}
	public void deleteInvoice(Invoice po) throws Exception{
		super.deleteObject(po);
	}
	public boolean isInvoiceDowloaded(String itemId,String refNumber){
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ");
		sb.append(getObjClass().getName().toString());
		sb.append(" t where t.ItemID ='").append(itemId).append("'");
		sb.append(" and t.RefNumber ='").append(refNumber).append("'");
		Query queryCount = super.getSession().createQuery(sb.toString());
		Number total = (Number)queryCount.uniqueResult();
		if(total.intValue() >0){
			return true;
		}else{
			return false;
		}
	}

}
